﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using DapperCRUD.Demo.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;

namespace DapperCRUD.Demo.Controllers
{
    public class DapperController : Controller
    {
        private string CONNECTION = "Data Source=/.;Initial Catalog=DapperCRUDs;User ID=sa;Password=/.;Trusted_Connection=True;MultipleActiveResultSets=true;Integrated Security=false;";
        public IActionResult Index()
        {
            return View();
        }
        /// <summary>
        /// C
        /// </summary>
        /// <param name="users"></param>
        /// <returns></returns>
        public JsonResult Create(Users users)
        {
            using (var connection = new SqlConnection(CONNECTION))
            {
                string myCommand = "insert into users (name,sex) values (@name,@sex);";
                connection.Execute(myCommand, users);
            }

            return Json("");
        }
        /// <summary>
        /// U
        /// </summary>
        /// <param name="users"></param>
        /// <returns></returns>
        public JsonResult Update(Users users)
        {
            using (var connection = new SqlConnection(CONNECTION))
            {
                string myCommand = "update users set name=@name,sex=@sex;";
                connection.Execute(myCommand, users);
            }

            return Json("");
        }
        /// <summary>
        /// R
        /// </summary>
        /// <returns></returns>
        public JsonResult Read()
        {
            List<Users> listUserses = new List<Users>();
            using (var connection = new SqlConnection(CONNECTION))
            {
                string myCommand = "select * from users;";
                listUserses = connection.Query<Users>(myCommand).ToList();
            }

            return Json(listUserses);
        }
        /// <summary>
        /// D
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public JsonResult Delete(int id)
        {
            using (var connection = new SqlConnection(CONNECTION))
            {
                string myCommand = "delete from users where id = @id";
                connection.Execute(myCommand, new{id});
            }

            return Json("");
        }
    }
}